package com.chinapost.service.impl;

import java.io.File;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.chinapost.dao.BaseDaoI;
import com.chinapost.excelUtil.DateUtil;
import com.chinapost.excelUtil.ExcelUtil;
import com.chinapost.po.Tdept;
import com.chinapost.po.Tdqcd;
import com.chinapost.po.Tdqlkt;
import com.chinapost.po.Tdqobk;
import com.chinapost.po.Tjfsz;
import com.chinapost.po.Tjhrw;
import com.chinapost.po.Tkind;
import com.chinapost.po.Tuser;
import com.chinapost.po.Twd;
import com.chinapost.po.Tycsj;
import com.chinapost.po.Tyxjl;
import com.chinapost.po.Tyxsj;
import com.chinapost.service.YxsjServiceI;
import com.chinapost.util.DoubleUtil;
import com.chinapost.vo.DataGrid;
import com.chinapost.vo.Yxsj;
import com.chinapost.vo.YxsjTj;

@Service("yxsjService")
public class YxsjServiceImpl implements YxsjServiceI {

	@Autowired
	private BaseDaoI<Tyxsj> yxsjDao;
	@Autowired
	private BaseDaoI<Tkind> kindDao;
	@Autowired
	private BaseDaoI<Tdept> deptDao;
	@Autowired
	private BaseDaoI<Tuser> userDao;
	@Autowired
	private BaseDaoI<Tdqcd> dqcdDao;
	@Autowired
	private BaseDaoI<Tdqlkt> dqlktDao;
	@Autowired
	private BaseDaoI<Tdqobk> dqobkDao;
	@Autowired
	private BaseDaoI<Tyxjl> yxjlDao;
	@Autowired
	private BaseDaoI<Tjfsz> jfszDao;
	@Autowired
	private BaseDaoI<Twd> wdDao;
	@Autowired
	private BaseDaoI<Tycsj> ycsjDao;
	@Autowired
	private BaseDaoI<YxsjTj> tjDao;
	@Autowired
	private BaseDaoI<Tjhrw> jhrwDao;

	@Override
	public String add(Yxsj yxsj) {
		String msg = "";

		// 1、先判断用户编号是否存在，不存在直接返回用户不存在
		Tuser tuser = getUserByBh(yxsj.getCbh());
		if (tuser == null) {
			msg = "用户不存在！";
			return msg;
		}
		// 2、判断账号位数是否过短，少于7位直接返回
		if (yxsj.getCzh().trim().length() < 7) {
			msg = "账号不能少于7位数！";
			return msg;
		}
		/**
		 * 3、根据账号和上报月份判断账号是否存在，不存在直接返回“账号不存在信息” 如果存在多条账号记录，整存整取定期返回“账号不唯一，输入更详细账号”，绿卡通和一本通则要全部添加账号数据
		 */
		Integer sbyf = yxsj.getCsbyf();
		// 获取上级单位的ID，用于查找属于该单位的网点机构号集合
		Tdept tdept = deptDao.get(Tdept.class, yxsj.getDeptid());
		String pdeptid = tdept.getTdept().getCid();
		switch (yxsj.getKindid()) {
		case 1:// 整存整取定期
			List<Tdqcd> list_dqcd = getDqcdByZh(yxsj.getCzh(), sbyf, pdeptid);
			if (list_dqcd.size() == 1) {
				Tdqcd tdqcd = list_dqcd.get(0);
				Tyxsj tyxsj = new Tyxsj();
				BeanUtils.copyProperties(tdqcd, tyxsj, new String[] { "czt" });
				tyxsj.setCbh(yxsj.getCbh());
				tyxsj.setTuserByCuserid(tuser);
				Tyxsj t = findYxsj(tyxsj);// 检查账号是否已经有人上报，防止重报
				if (t == null) {
					tyxsj = setYxsj(yxsj, tyxsj);
					Double jf = yeToJf(tyxsj);// 换算积分
					tyxsj.setCjf(jf);
					Double yxjl = getYxjl(tyxsj);// 计算每积分奖励
					tyxsj.setCyxfy(jf * yxjl);
					yxsjDao.save(tyxsj);
					msg = "添加成功！";
					return msg;
				} else {
					msg = "该账号" + t.getTuserByCuserid().getCname() + "(" + t.getTuserByCuserid().getCbh() + ")已上报！";
					return msg;
				}

			} else {
				if (list_dqcd.size() == 0) {
					msg = "账号不存在！";
					return msg;
				} else {
					msg = "账号不唯一，请输入更多位数账号！";
					return msg;
				}
			}
		case 2:// 绿卡通定期
			List<Tdqlkt> list_dqlkt = getDqlktByZh(yxsj.getCzh(), sbyf, pdeptid);
			if (list_dqlkt.size() == 1) {
				Tdqlkt tdqlkt = list_dqlkt.get(0);
				Tyxsj tyxsj = new Tyxsj();
				BeanUtils.copyProperties(tdqlkt, tyxsj, new String[] { "czt" });
				tyxsj.setCbh(yxsj.getCbh());
				tyxsj.setTuserByCuserid(tuser);
				Tyxsj t = findYxsj(tyxsj);// 检查账号是否已经有人上报，防止重报
				if (t == null) {
					tyxsj = setYxsj(yxsj, tyxsj);
					Double jf = yeToJf(tyxsj);// 换算积分
					tyxsj.setCjf(jf);
					Double yxjl = getYxjl(tyxsj);// 计算每积分奖励
					tyxsj.setCyxfy(jf * yxjl);
					yxsjDao.save(tyxsj);
					msg = "添加成功！";
					return msg;
				} else {
					msg = "该账号" + t.getTuserByCuserid().getCname() + "(" + t.getTuserByCuserid().getCbh() + ")已上报！";
					return msg;
				}
			} else {
				if (list_dqlkt.size() == 0) {
					msg = "账号不存在！";
					return msg;
				} else {// 如果查到多条绿卡通数据，全部增加
					for (Tdqlkt tdqlkt : list_dqlkt) {
						Tyxsj tyxsj = new Tyxsj();
						BeanUtils.copyProperties(tdqlkt, tyxsj, new String[] { "czt" });
						tyxsj.setCbh(yxsj.getCbh());
						tyxsj.setTuserByCuserid(tuser);
						Tyxsj t = findYxsj(tyxsj);// 检查账号是否已经有人上报，防止重报
						if (t == null) {
							tyxsj = setYxsj(yxsj, tyxsj);
							Double jf = yeToJf(tyxsj);// 换算积分
							tyxsj.setCjf(jf);
							Double yxjl = getYxjl(tyxsj);// 计算每积分奖励
							tyxsj.setCyxfy(jf * yxjl);
							yxsjDao.save(tyxsj);
						} else {
							msg = "该账号" + t.getTuserByCuserid().getCname() + "(" + t.getTuserByCuserid().getCbh() + ")已上报！";
							return msg;
						}
					}
					msg = "添加成功" + list_dqlkt.size() + "条绿卡通数据！";
					return msg;
				}
			}
		case 3:// 一本通定期
			List<Tdqobk> list_dqobk = getDqobkByZh(yxsj.getCzh(), sbyf, pdeptid);
			if (list_dqobk.size() == 1) {
				Tdqobk tdqobk = list_dqobk.get(0);
				Tyxsj tyxsj = new Tyxsj();
				BeanUtils.copyProperties(tdqobk, tyxsj, new String[] { "czt" });
				tyxsj.setCbh(yxsj.getCbh());
				tyxsj.setTuserByCuserid(tuser);
				tyxsj.setCkh(tdqobk.getCybt());
				Tyxsj t = findYxsj(tyxsj);// 检查账号是否已经有人上报，防止重报
				if (t == null) {
					tyxsj = setYxsj(yxsj, tyxsj);
					Double jf = yeToJf(tyxsj);// 换算积分
					tyxsj.setCjf(jf);
					Double yxjl = getYxjl(tyxsj);// 计算每积分奖励
					tyxsj.setCyxfy(jf * yxjl);
					yxsjDao.save(tyxsj);
					msg = "添加成功！";
					return msg;
				} else {
					msg = "该账号" + t.getTuserByCuserid().getCname() + "(" + t.getTuserByCuserid().getCbh() + ")已上报！";
					return msg;
				}
			} else {
				if (list_dqobk.size() == 0) {
					msg = "账号不存在！";
					return msg;
				} else {// 如果查到多条一本通数据，全部增加
					for (Tdqobk tdqobk : list_dqobk) {
						Tyxsj tyxsj = new Tyxsj();
						BeanUtils.copyProperties(tdqobk, tyxsj, new String[] { "czt" });
						tyxsj.setCkh(tdqobk.getCybt());
						tyxsj.setCbh(yxsj.getCbh());
						tyxsj.setTuserByCuserid(tuser);
						Tyxsj t = findYxsj(tyxsj);// 检查账号是否已经有人上报，防止重报
						if (t == null) {
							tyxsj = setYxsj(yxsj, tyxsj);
							Double jf = yeToJf(tyxsj);// 换算积分
							tyxsj.setCjf(jf);
							Double yxjl = getYxjl(tyxsj);// 计算每积分奖励
							tyxsj.setCyxfy(jf * yxjl);
							yxsjDao.save(tyxsj);
						} else {
							msg = "该账号" + t.getTuserByCuserid().getCname() + "(" + t.getTuserByCuserid().getCbh() + ")已上报！";
							return msg;
						}
					}
					msg = "添加成功" + list_dqobk.size() + "条一本通数据！";
					return msg;
				}
			}
		default:
			msg = "业务种类错误！";
			return msg;
		}
	}

	/**
	 * 设置yxsj
	 */
	private Tyxsj setYxsj(Yxsj yxsj, Tyxsj t) {
		t.setCsbyf(yxsj.getCsbyf());
		Date d = new Date();
		t.setCid(UUID.randomUUID().toString());
		t.setCzt("正常");
		t.setCcreatedate(new Timestamp(d.getTime()));
		t.setCmodifydate(new Timestamp(d.getTime()));
		t.setTdept(deptDao.get(Tdept.class, yxsj.getDeptid()));
		t.setTkind(kindDao.get(Tkind.class, yxsj.getKindid()));
		if (!StringUtils.isEmpty(yxsj.getCreateuserid())) {
			t.setTuserByCcreateuserid(userDao.get(Tuser.class, yxsj.getCreateuserid()));
			t.setTuserByCmodifyuserid(userDao.get(Tuser.class, yxsj.getCreateuserid()));
		}
		return t;
	}

	/**
	 * 通过编号查找用户
	 * 
	 * @param bh
	 * @return
	 */
	private Tuser getUserByBh(String bh) {
		String hql = "from Tuser t where t.cbh =:bh";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("bh", bh);
		return userDao.get(hql, params);
	}

	/**
	 * 通过账号和月份,以及单位所属的网点机构号集合，查找定期整存整取数据
	 * 
	 * @param zh
	 * @return
	 */
	private List<Tdqcd> getDqcdByZh(String zh, Integer sbyf, String deptid) {
		String hql = "from Tdqcd t where  t.czt=0 and t.czh like :zh and substring(t.cqxr,1,6)= :sbyf and t.ckhj in (:jghs)";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("zh", "%%" + zh + "%%");
		params.put("sbyf", sbyf.toString());
		params.put("jghs", listJghByDeptid(deptid));
		return dqcdDao.find(hql, params);
	}

	/**
	 * 通过账号和月份,以及单位所属的网点机构号集合，查找定期绿卡通数据（ 前台传过来的账号为绿卡通号码）
	 * 
	 * @param zh
	 * @return
	 */
	private List<Tdqlkt> getDqlktByZh(String zh, Integer sbyf, String deptid) {
		String hql = "from Tdqlkt t where t.czt=0 and  t.ckh like :zh and substring(t.cqxr,1,6)= :sbyf and t.ckhj in (:jghs)";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("zh", "%%" + zh + "%%");
		params.put("sbyf", sbyf.toString());
		params.put("jghs", listJghByDeptid(deptid));
		return dqlktDao.find(hql, params);
	}

	/**
	 * 通过账号和月份,以及单位所属的网点机构号集合，查找定期一本通数据（前台传过来的账号为一本通号）
	 * 
	 * @param zh
	 * @return
	 */
	private List<Tdqobk> getDqobkByZh(String zh, Integer sbyf, String deptid) {
		String hql = "from Tdqobk t where t.czt=0 and  t.cybt like :zh and substring(t.cqxr,1,6)= :sbyf and t.ckhj in (:jghs)";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("zh", "%%" + zh + "%%");
		params.put("sbyf", sbyf.toString());
		params.put("jghs", listJghByDeptid(deptid));
		return dqobkDao.find(hql, params);
	}

	/**
	 * 根据单位的id查找出下属的所有网点的机构号集合
	 */
	private List<String> listJghByDeptid(String deptid) {
		List<String> jghs = new ArrayList<String>();
		String hql = "from Twd t where t.tdept.cid in(:ids)";
		Map<String, Object> params = new HashMap<String, Object>();
		List<String> ids = getAllChildTdeptIds(deptid);// 获取所有的下属单位id
		params.put("ids", ids);
		List<Twd> twds = wdDao.find(hql, params);
		if (twds != null && twds.size() > 0) {
			for (Twd t : twds) {
				jghs.add(t.getCjgh());
			}
		}
		return jghs;
	}

	/**
	 * 通过起息日、单位、业务种类来得出积分的营销奖励
	 */
	private Double getYxjl(Tyxsj tyxsj) {
		// 查询规则：起息日必须大于等于开始日期，小于等于结束日期，金额要大于等于最小金额，小于最大金额，创建的单位id相等，业务种类也必须相等。
		String hql = " from Tyxjl t where t.cstartdate<=:qxr and t.cenddate>=:qxr and t.tdept.cid =:deptid ";
		hql += " and t.cmin<= :jf and t.cmax >:jf and t.tkind.cid =:kindid";
		Map<String, Object> params = new HashMap<String, Object>();
		// 获取单位上级单位的ID，用于查找营销奖励，营销奖励设置是由所在部门的上级单位创建
		params.put("deptid", tyxsj.getTdept().getTdept().getCid());
		Timestamp qxr = new Timestamp(DateUtil.toDate(tyxsj.getCqxr()).getTime());
		params.put("qxr", qxr);
		params.put("jf", tyxsj.getCjf());
		params.put("kindid", tyxsj.getTkind().getCid());
		Tyxjl tyxjl = yxjlDao.get(hql, params);
		if (tyxjl != null) {
			return tyxjl.getCfee();
		}
		return 0.0;
	}

	/**
	 * 账号余额换算成积分
	 */
	private Double yeToJf(Tyxsj tyxsj) {
		String hql = "from Tjfsz t where t.tdept.cid =:deptid and t.tkind.cid =:kindid";
		Map<String, Object> params = new HashMap<String, Object>();
		// 获取单位上级单位的ID，用于查找积分规则，积分规则是由上级单位创建
		params.put("deptid", tyxsj.getTdept().getTdept().getCid());
		params.put("kindid", tyxsj.getTkind().getCid());
		Tjfsz tjfsz = jfszDao.get(hql, params);
		if (tjfsz != null) {
			Double cjf = tjfsz.getCjf();
			Double cje = tjfsz.getCje();
			if (tyxsj.getCcq() <= 12) {// 少于1年的除以12再乘存期
				if (tyxsj.getCcq() != 7) {
					return DoubleUtil.Keep2(tyxsj.getCye() * tyxsj.getCcq() * cjf / (12 * cje));
				} else {// 7天存款不算积分
					return 0.0;
				}
			} else {// 大于等于12个月的算1年
				return DoubleUtil.Keep2(tyxsj.getCye() * cjf / cje);
			}
		} else
			return 0.0;
	}

	@Override
	public void edit(Yxsj yxsj) {
		Tyxsj t = yxsjDao.get(Tyxsj.class, yxsj.getCid());
		Date d = new Date();
		t.setCmodifydate(new Timestamp(d.getTime()));
		t.setCbh(yxsj.getCbh());
		t.setTuserByCuserid(getUserByBh(yxsj.getCbh()));
		t.setTdept(deptDao.get(Tdept.class, yxsj.getDeptid()));
		if (!StringUtils.isEmpty(yxsj.getModifyuserid())) {
			t.setTuserByCmodifyuserid(userDao.get(Tuser.class, yxsj.getModifyuserid()));
		}

	}

	@Override
	public void delete(Yxsj yxsj) {
		if (yxsj.getIds() != null) {
			List<String> cids = new ArrayList<String>();
			String ids = yxsj.getIds();
			for (String cid : ids.split(",")) {
				cids.add(cid);
			}
			String hql = "delete Tyxsj t where t.cid in (:cids)";
			Map<String, Object> params = new HashMap<String, Object>();
			params.put("cids", cids);
			yxsjDao.executeHql(hql, params);
		}
	}

	@Override
	public DataGrid datagrid(Yxsj yxsj) {
		DataGrid dg = new DataGrid();
		dg.setRows(listRows(find(yxsj)));
		dg.setTotal(total(yxsj));
		return dg;
	}

	private Long total(Yxsj yxsj) {
		String hql = "select count(*) from Tyxsj t where 1=1";
		Map<String, Object> params = new HashMap<String, Object>();
		hql = addWhere(yxsj, hql, params);
		return yxsjDao.count(hql, params);
	}

	private List<Tyxsj> find(Yxsj yxsj) {
		String hql = "from Tyxsj t where 1=1";
		Map<String, Object> params = new HashMap<String, Object>();
		hql = addWhere(yxsj, hql, params);
		if (yxsj.getOrder() != null && yxsj.getSort() != null) {
			hql += " order by " + yxsj.getSort() + " " + yxsj.getOrder();
		}
		return yxsjDao.find(hql, params, yxsj.getPage(), yxsj.getRows());
	}

	private List<Yxsj> listRows(List<Tyxsj> lt) {
		List<Yxsj> rows = new ArrayList<Yxsj>();
		if (lt != null && lt.size() > 0) {
			for (Tyxsj t : lt) {
				Yxsj sub = new Yxsj();
				BeanUtils.copyProperties(t, sub);
				sub.setCzh("**" + t.getCzh().substring(t.getCzh().length() - 7));
				if (!StringUtils.isEmpty(t.getCkh())) {
					sub.setCkh("**" + t.getCkh().substring(t.getCkh().length() - 7));
				}
				sub.setDeptid(t.getTdept().getCid());
				sub.setDeptname(t.getTdept().getCname());
				sub.setKindid(t.getTkind().getCid());
				sub.setKindname(t.getTkind().getCname());
				sub.setUserid(t.getTuserByCuserid().getCid());
				sub.setUsername(t.getTuserByCuserid().getCname());
				rows.add(sub);
			}
		}
		return rows;
	}

	private String addWhere(Yxsj yxsj, String hql, Map<String, Object> params) {
		if (yxsj != null && yxsj.getCbh() != null && !yxsj.getCbh().trim().equals("")) {
			hql += " and t.cbh = :bh ";
			params.put("bh", yxsj.getCbh());
		}
		if (yxsj != null && yxsj.getDeptIds() != null && yxsj.getDeptIds().size() > 0) {
			hql += " and t.tdept.cid in(:deptids)";
			params.put("deptids", yxsj.getDeptIds());
		}
		if (yxsj != null && yxsj.getStartMonth() != null) {
			hql += " and t.csbyf >= :startmonth";
			params.put("startmonth", yxsj.getStartMonth());
		}
		if (yxsj != null && yxsj.getEndMonth() != null) {
			hql += " and t.csbyf <= :endmonth";
			params.put("endmonth", yxsj.getEndMonth());
		}
		if (yxsj != null && !StringUtils.isEmpty(yxsj.getCzh())) {
			hql += " and (t.czh like :zh or t.ckh like :zh)";
			params.put("zh", "%%" + yxsj.getCzh() + "%%");
		}
		return hql;
	}

	/**
	 * 根据起息日和账号来判断该账号是否已经有人上报，返回null表示没人上报，否则已报 由于存在3个月的账号，一年内可能会多次上报，所以要加上起息日来判断
	 */
	private Tyxsj findYxsj(Tyxsj yxsj) {
		String hql = " from Tyxsj t where  t.cqxr = :qxr  and t.czh =:zh";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("qxr", yxsj.getCqxr());
		params.put("zh", yxsj.getCzh());
		return yxsjDao.get(hql, params);
	}

	/**
	 * 批量上传excel文件导入营销数据
	 */
	@Override
	public Integer addYxsjs(Yxsj yxsj) {
		int count = 0;
		if (yxsj.getFilenames() != null) {
			String files = yxsj.getFilenames();
			Integer sbyf = yxsj.getCsbyf();
			String filepath = yxsj.getFilepath();
			String createuserid = yxsj.getCreateuserid();
			String deptid = yxsj.getDeptid();
			for (String file : files.split(",")) {
				String filename = filepath + File.separator + file;
				count += addYxsj(filename, sbyf, createuserid, deptid);
				// 导入完成后删除上传的营销数据文件
				File f = new File(filename);
				if (f.exists() && f.isFile()) {
					f.delete();
				}
			}
		}
		return count;
	}

	/**
	 * 根据文件名读取营销数据文件，然后逐行导入
	 * 
	 * @param filename
	 * @param sbyf
	 * @param createuserid
	 * @param deptid
	 * @return
	 */
	private int addYxsj(String filename, Integer sbyf, String createuserid, String deptid) {
		int i = 0;
		ExcelUtil eu = new ExcelUtil();
		List<ArrayList<String>> yxsjList = eu.read(filename, 3);// 前3行忽略，从第4行开始读取
		// 读取每一行营销数据
		for (ArrayList<String> row : yxsjList) {
			String bh = row.get(1).trim();
			String kindid = row.get(2).trim();
			String zh = row.get(3).trim();
			//判断zh是否大于13位，如果大于13位截取最右边的13位
			if (zh.length() > 13 && zh.length() <20){
				zh = zh.substring(zh.length()-13,zh.length());
			}
			// 判断数据格式是否符合要求，编号和业务种类不能为空，账号大于等于7位数
			if (bh.length() > 0 && kindid.length() > 0 && zh.length() > 6) {
				Tuser tuser = getUserByBh(bh);
				// 判断用户编号是否存在,且该用户所在单位必须在上传用户所在单位的下属单位中
				if (tuser != null && isChildDept(tuser.getTdept().getCid(), deptid)) {
					Integer kind = Integer.parseInt(kindid);
					switch (kind) {
					case 1:// 普通定期
						List<Tdqcd> list_dqcd = getDqcdByZh(zh, sbyf, deptid);
						if (list_dqcd.size() == 1) {
							Tdqcd tdqcd = list_dqcd.get(0);
							Tyxsj tyxsj = new Tyxsj();
							BeanUtils.copyProperties(tdqcd, tyxsj, new String[] { "czt" });
							tyxsj.setCbh(bh);
							tyxsj.setTuserByCuserid(tuser);
							Tyxsj t = findYxsj(tyxsj);// 检查账号是否已经有人上报，防止重报
							if (t == null) {// 没有找到则添加该账号和用户编号
								tyxsj.setCsbyf(sbyf);
								Date d = new Date();
								tyxsj.setCid(UUID.randomUUID().toString());
								tyxsj.setCzt("正常");
								tyxsj.setCcreatedate(new Timestamp(d.getTime()));
								tyxsj.setCmodifydate(new Timestamp(d.getTime()));
								tyxsj.setTdept(tuser.getTdept());
								tyxsj.setTkind(kindDao.get(Tkind.class, 1));
								tyxsj.setTuserByCcreateuserid(userDao.get(Tuser.class, createuserid));
								tyxsj.setTuserByCmodifyuserid(userDao.get(Tuser.class, createuserid));
								Double jf = yeToJf(tyxsj);// 换算积分
								tyxsj.setCjf(jf);
								Double yxjl = getYxjl(tyxsj);// 计算每积分奖励
								tyxsj.setCyxfy(jf * yxjl);
								yxsjDao.save(tyxsj);
								i++;
							} else {// 如果找到重复,检查上报的员工编号是否相同，不同则写入异常数据表中
								if (!bh.equals(t.getCbh())) {// 如果找到的重复数据用户编号不一样记录异常数据，一样则是属于重复导入，忽略该记录
									if (!findYcsj(bh, zh, sbyf)) {// 查看异常数据是否存在，不存在则添加 ,存在则忽略
										Tycsj tycsj = new Tycsj();
										tycsj.setCid(UUID.randomUUID().toString());
										tycsj.setCbh(bh);
										tycsj.setTuserByCuserid(tuser);
										tycsj.setCsbyf(sbyf);
										tycsj.setCzh(zh);
										tycsj.setCcq(t.getCcq());
										tycsj.setCkhj(t.getCkhj());
										tycsj.setCye(t.getCye());
										Date d = new Date();
										tycsj.setCcreatedate(new Timestamp(d.getTime()));
										tycsj.setCmodifydate(new Timestamp(d.getTime()));
										tycsj.setTdept(tuser.getTdept());
										tycsj.setTkind(kindDao.get(Tkind.class, 1));
										tycsj.setTuserByCcreateuserid(userDao.get(Tuser.class, createuserid));
										tycsj.setTuserByCmodifyuserid(userDao.get(Tuser.class, createuserid));
										String yy = "与" + t.getTuserByCuserid().getCname() + "(" + t.getTuserByCuserid().getCbh() + ")重复上报";
										tycsj.setCyy(yy);
										ycsjDao.save(tycsj);
									}
								}
							}

						} else {
							if (list_dqcd.size() == 0) {// 账号不存在则添加到异常数据表
								if (!findYcsj(bh, zh, sbyf)) {// 查看异常数据是否存在，不存在则添加
									Tycsj tycsj = new Tycsj();
									tycsj.setCid(UUID.randomUUID().toString());
									tycsj.setCbh(bh);
									tycsj.setTuserByCuserid(tuser);
									tycsj.setCsbyf(sbyf);
									tycsj.setCzh(zh);
									Date d = new Date();
									tycsj.setCcreatedate(new Timestamp(d.getTime()));
									tycsj.setCmodifydate(new Timestamp(d.getTime()));
									tycsj.setTdept(tuser.getTdept());
									tycsj.setTkind(kindDao.get(Tkind.class, 1));
									tycsj.setTuserByCcreateuserid(userDao.get(Tuser.class, createuserid));
									tycsj.setTuserByCmodifyuserid(userDao.get(Tuser.class, createuserid));
									String yy = "账号不存在！";
									tycsj.setCyy(yy);
									ycsjDao.save(tycsj);
								}
							} else {// 找到多条账号记录，需提供更详细账号
								if (!findYcsj(bh, zh, sbyf)) {// 查看异常数据是否存在，不存在则添加
									Tycsj tycsj = new Tycsj();
									tycsj.setCid(UUID.randomUUID().toString());
									tycsj.setCbh(bh);
									tycsj.setTuserByCuserid(tuser);
									tycsj.setCsbyf(sbyf);
									tycsj.setCzh(zh);
									Date d = new Date();
									tycsj.setCcreatedate(new Timestamp(d.getTime()));
									tycsj.setCmodifydate(new Timestamp(d.getTime()));
									tycsj.setTdept(tuser.getTdept());
									tycsj.setTkind(kindDao.get(Tkind.class, 1));
									tycsj.setTuserByCcreateuserid(userDao.get(Tuser.class, createuserid));
									tycsj.setTuserByCmodifyuserid(userDao.get(Tuser.class, createuserid));
									String yy = "账号不唯一，需提供更多位数账号！";
									tycsj.setCyy(yy);
									ycsjDao.save(tycsj);
								}
							}
						}
						break;
					case 2:// 绿卡通
						List<Tdqlkt> list_dqlkt = getDqlktByZh(zh, sbyf, deptid);
						if (list_dqlkt.size() == 0) {
							if (!findYcsj(bh, zh, sbyf)) {// 查看异常数据是否存在，不存在则添加
								Tycsj tycsj = new Tycsj();
								tycsj.setCid(UUID.randomUUID().toString());
								tycsj.setCbh(bh);
								tycsj.setTuserByCuserid(tuser);
								tycsj.setCsbyf(sbyf);
								tycsj.setCzh(zh);
								Date d = new Date();
								tycsj.setCcreatedate(new Timestamp(d.getTime()));
								tycsj.setCmodifydate(new Timestamp(d.getTime()));
								tycsj.setTdept(tuser.getTdept());
								tycsj.setTkind(kindDao.get(Tkind.class, 2));
								tycsj.setTuserByCcreateuserid(userDao.get(Tuser.class, createuserid));
								tycsj.setTuserByCmodifyuserid(userDao.get(Tuser.class, createuserid));
								String yy = "账号不存在！";
								tycsj.setCyy(yy);
								ycsjDao.save(tycsj);
							}
						} else {// 如果查到多条绿卡通数据，全部增加
							for (Tdqlkt tdqlkt : list_dqlkt) {
								Tyxsj tyxsj = new Tyxsj();
								BeanUtils.copyProperties(tdqlkt, tyxsj, new String[] { "czt" });
								tyxsj.setCbh(bh);
								tyxsj.setTuserByCuserid(tuser);
								Tyxsj t = findYxsj(tyxsj);// 检查账号是否已经有人上报，防止重报
								if (t == null) {
									tyxsj.setCsbyf(sbyf);
									Date d = new Date();
									tyxsj.setCid(UUID.randomUUID().toString());
									tyxsj.setCzt("正常");
									tyxsj.setCcreatedate(new Timestamp(d.getTime()));
									tyxsj.setCmodifydate(new Timestamp(d.getTime()));
									tyxsj.setTdept(tuser.getTdept());
									tyxsj.setTkind(kindDao.get(Tkind.class, 2));
									tyxsj.setTuserByCcreateuserid(userDao.get(Tuser.class, createuserid));
									tyxsj.setTuserByCmodifyuserid(userDao.get(Tuser.class, createuserid));
									Double jf = yeToJf(tyxsj);// 换算积分
									tyxsj.setCjf(jf);
									Double yxjl = getYxjl(tyxsj);// 计算每积分奖励
									tyxsj.setCyxfy(jf * yxjl);
									yxsjDao.save(tyxsj);
									i++;
								} else {
									if (!bh.equals(t.getCbh())) {// 如果编号不一样记录异常数据，编号一样则是重复导入，忽略该记录
										if (!findYcsj(bh, zh, sbyf)) {// 查看异常数据是否存在，不存在则添加
											Tycsj tycsj = new Tycsj();
											tycsj.setCid(UUID.randomUUID().toString());
											tycsj.setCbh(bh);
											tycsj.setTuserByCuserid(tuser);
											tycsj.setCsbyf(sbyf);
											tycsj.setCzh(zh);
											tycsj.setCcq(t.getCcq());
											tycsj.setCkhj(t.getCkhj());
											tycsj.setCye(t.getCye());
											Date d = new Date();
											tycsj.setCcreatedate(new Timestamp(d.getTime()));
											tycsj.setCmodifydate(new Timestamp(d.getTime()));
											tycsj.setTdept(tuser.getTdept());
											tycsj.setTkind(kindDao.get(Tkind.class, 2));
											tycsj.setTuserByCcreateuserid(userDao.get(Tuser.class, createuserid));
											tycsj.setTuserByCmodifyuserid(userDao.get(Tuser.class, createuserid));
											String yy = "与" + t.getTuserByCuserid().getCname() + "(" + t.getTuserByCuserid().getCbh() + ")重复上报";
											tycsj.setCyy(yy);
											ycsjDao.save(tycsj);
										}
									}
								}
							}
						}
						break;
					case 3:// 一本 通
						List<Tdqobk> list_dqobk = getDqobkByZh(zh, sbyf, deptid);
						if (list_dqobk.size() == 0) {// 未找到，添加“账号不存在”异常
							if (!findYcsj(bh, zh, sbyf)) {// 查看异常数据是否存在，不存在则添加
								Tycsj tycsj = new Tycsj();
								tycsj.setCid(UUID.randomUUID().toString());
								tycsj.setCbh(bh);
								tycsj.setTuserByCuserid(tuser);
								tycsj.setCsbyf(sbyf);
								tycsj.setCzh(zh);
								Date d = new Date();
								tycsj.setCcreatedate(new Timestamp(d.getTime()));
								tycsj.setCmodifydate(new Timestamp(d.getTime()));
								tycsj.setTdept(tuser.getTdept());
								tycsj.setTkind(kindDao.get(Tkind.class, 3));
								tycsj.setTuserByCcreateuserid(userDao.get(Tuser.class, createuserid));
								tycsj.setTuserByCmodifyuserid(userDao.get(Tuser.class, createuserid));
								String yy = "账号不存在！";
								tycsj.setCyy(yy);
								ycsjDao.save(tycsj);
							}
						} else {// 如果查到多条数据，全部增加
							for (Tdqobk tdqobk : list_dqobk) {
								Tyxsj tyxsj = new Tyxsj();
								BeanUtils.copyProperties(tdqobk, tyxsj, new String[] { "czt" });
								tyxsj.setCkh(tdqobk.getCybt());// 添加一本通号码到kh字段中
								tyxsj.setCbh(bh);
								tyxsj.setTuserByCuserid(tuser);
								Tyxsj t = findYxsj(tyxsj);// 检查账号是否已经有人上报，防止重报
								if (t == null) {
									tyxsj.setCsbyf(sbyf);
									Date d = new Date();
									tyxsj.setCid(UUID.randomUUID().toString());
									tyxsj.setCzt("正常");
									tyxsj.setCcreatedate(new Timestamp(d.getTime()));
									tyxsj.setCmodifydate(new Timestamp(d.getTime()));
									tyxsj.setTdept(tuser.getTdept());
									tyxsj.setTkind(kindDao.get(Tkind.class, 3));
									tyxsj.setTuserByCcreateuserid(userDao.get(Tuser.class, createuserid));
									tyxsj.setTuserByCmodifyuserid(userDao.get(Tuser.class, createuserid));
									Double jf = yeToJf(tyxsj);// 换算积分
									tyxsj.setCjf(jf);
									Double yxjl = getYxjl(tyxsj);// 计算每积分奖励
									tyxsj.setCyxfy(jf * yxjl);
									yxsjDao.save(tyxsj);
									i++;
								} else {
									if (!bh.equals(t.getCbh())) {// 如果编号不一样记录异常数据，编号一样则是重复导入，忽略该记录
										if (!findYcsj(bh, zh, sbyf)) {// 查看异常数据是否存在，不存在则添加
											Tycsj tycsj = new Tycsj();
											tycsj.setCid(UUID.randomUUID().toString());
											tycsj.setCbh(bh);
											tycsj.setTuserByCuserid(tuser);
											tycsj.setCsbyf(sbyf);
											tycsj.setCzh(zh);
											tycsj.setCcq(t.getCcq());
											tycsj.setCkhj(t.getCkhj());
											tycsj.setCye(t.getCye());
											Date d = new Date();
											tycsj.setCcreatedate(new Timestamp(d.getTime()));
											tycsj.setCmodifydate(new Timestamp(d.getTime()));
											tycsj.setTdept(tuser.getTdept());
											tycsj.setTkind(kindDao.get(Tkind.class, 3));
											tycsj.setTuserByCcreateuserid(userDao.get(Tuser.class, createuserid));
											tycsj.setTuserByCmodifyuserid(userDao.get(Tuser.class, createuserid));
											String yy = "与" + t.getTuserByCuserid().getCname() + "(" + t.getTuserByCuserid().getCbh() + ")重复上报";
											tycsj.setCyy(yy);
											ycsjDao.save(tycsj);
										}
									}
								}
							}
						}
						break;
					default:
						break;
					}
				}
			}
		}
		return i;
	}

	/**
	 * 根据一个单位的id，获取它本身以及下属的所有的单位id集合
	 * 
	 * @param deptid
	 * @param depts
	 * @return
	 */
	private List<Tdept> getAllChildTdepts(String deptid, List<Tdept> depts) {
		Tdept dept = deptDao.get(Tdept.class, deptid);
		depts.add(dept);
		Set<Tdept> tdepts = dept.getTdepts();
		List<Tdept> list = new ArrayList<Tdept>(tdepts);
		if (list != null && list.size() > 0) {
			for (Tdept t : list) {
				getAllChildTdepts(t.getCid(), depts);
			}
		}
		return depts;
	}

	private List<String> getAllChildTdeptIds(String deptid) {
		List<String> ids = new ArrayList<String>();
		List<Tdept> depts = new ArrayList<Tdept>();
		depts = getAllChildTdepts(deptid, depts);
		for (Tdept t : depts) {
			ids.add(t.getCid());
		}
		return ids;
	}

	/**
	 * 判断前一个单位id是否在后一个单位id的下属单位中
	 * 
	 * @param cdeptid
	 * @param pdeptid
	 * @return
	 */
	private boolean isChildDept(String cdeptid, String pdeptid) {
		List<Tdept> tdepts = new ArrayList<Tdept>();
		getAllChildTdepts(pdeptid, tdepts);
		for (Tdept t : tdepts) {
			if (cdeptid.equals(t.getCid())) {
				return true;
			}
		}
		return false;
	}

	private boolean findYcsj(String bh, String zh, Integer sbyf) {
		String hql = "from Tycsj t where t.cbh = :bh and t.csbyf =:sbyf and t.czh like :zh";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("zh", "%%" + zh + "%%");
		params.put("sbyf", sbyf);
		params.put("bh", bh);
		Tycsj t = ycsjDao.get(hql, params);
		if (t != null) {
			return true;
		}
		return false;
	}

	/**
	 * 根据条件查询出需要导出excel的数据
	 */
	@Override
	public List<Yxsj> excelData(Yxsj yxsj) {
		String hql = "from Tyxsj t where 1=1 ";
		Map<String, Object> params = new HashMap<String, Object>();
		hql = addWhere(yxsj, hql, params);
		hql += " order by t.cbh,t.csbyf";
		List<Tyxsj> yxsjs = yxsjDao.find(hql, params);
		return listRows(yxsjs);
	}

	/**
	 * 按年份统计出个人营销总积分和完成任务的百分比
	 */
	@Override
	public DataGrid datagrid_tjgr(Yxsj yxsj) {
		DataGrid dg = new DataGrid();
		String hql = "select new com.chinapost.vo.YxsjTj(t.cbh,sum(t.cjf)) from Tyxsj t where 1=1 ";
		Map<String, Object> params = new HashMap<String, Object>();
		hql = addWhere(yxsj, hql, params);
		hql += " group by t.cbh order by  t.cbh";
		List<YxsjTj> tjs = tjDao.find(hql, params, yxsj.getPage(), yxsj.getRows());
		Integer year = Integer.parseInt(yxsj.getStartMonth().toString().substring(0, 4));
		dg.setRows(setYxsjTj(tjs, year));
		dg.setTotal(total_tjgr(yxsj));
		return dg;
	}
	
	private Long total_tjgr (Yxsj yxsj){
		String hql = "select new com.chinapost.vo.YxsjTj(t.cbh,sum(t.cjf)) from Tyxsj t where 1=1 ";
		Map<String, Object> params = new HashMap<String, Object>();
		hql = addWhere(yxsj, hql, params);
		hql += "  group by t.cbh ";
		List<YxsjTj> tjs = tjDao.find(hql, params);
		if (null != tjs && tjs.size() >0){
			return (long)tjs.size();
		}
		return 0L;
	}

	private List<YxsjTj> setYxsjTj(List<YxsjTj> lists, Integer year) {
		String hql = " from Tjhrw t where t.cyear =:year and t.tyglb.cid =:yglbid and t.tdept.cid =:deptid";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("year", year);
		for (YxsjTj tj : lists) {
			Tuser tuser = getUserByBh(tj.getCbh());
			tj.setDeptname(tuser.getTdept().getCname());
			tj.setUsername(tuser.getCname());
			params.put("yglbid", tuser.getTyglb().getCid());
			params.put("deptid", tuser.getTdept().getTdept().getCid());// 计划任务一般由所在部门的上一级单位设置
			Tjhrw tjhrw = jhrwDao.get(hql, params);
			if (tjhrw != null) {
				tj.setCjh(tjhrw.getCzjf());
				tj.setCbl(DoubleUtil.Keep2(tj.getCzjf() * 100 / tj.getCjh()));// 计算任务完成百分比
			} else {
				tj.setCjh(0.0);
			}
		}
		return lists;
	}

	/**
	 * 统计个人营销任务完成情况
	 */
	@Override
	public List<YxsjTj> excelData_tjgr(Yxsj yxsj) {
		String hql = "select new com.chinapost.vo.YxsjTj(t.cbh,sum(t.cjf)) from Tyxsj t where 1=1 ";
		Map<String, Object> params = new HashMap<String, Object>();
		hql = addWhere(yxsj, hql, params);
		hql += " group by t.cbh order by t.cbh";
		List<YxsjTj> tjs = tjDao.find(hql, params);
		// 将得到的list转为Map
		Map<String, Double> tjmap = new HashMap<String, Double>();
		for (YxsjTj tj : tjs) {
			tjmap.put(tj.getCbh(), tj.getCzjf());
		}
		String user_hql = "select new com.chinapost.vo.YxsjTj(t.tdept.cname,t.cbh,t.cname) from Tuser t where t.ckind=2 and  t.tdept.cid in (:deptids) ";
		Map<String, Object> user_params = new HashMap<String, Object>();
		if (!StringUtils.isEmpty(yxsj.getCbh())){
			user_hql += " and t.cbh =:bh ";
			user_params.put("bh", yxsj.getCbh());
		}
		user_hql += " order by t.tdept.cseq,t.cbh";
		user_params.put("deptids", yxsj.getDeptIds());
		List<YxsjTj> lists = tjDao.find(user_hql, user_params);
		if (lists != null && lists.size() > 0) {
			for (YxsjTj tj : lists) {
				if (tjmap.get(tj.getCbh()) != null) {// 如果员工有积分，则设置积分，没有则设为0
					tj.setCzjf(tjmap.get(tj.getCbh()));
				} else {
					tj.setCzjf(0.0);
				}
			}
		}
		Integer year = Integer.parseInt(yxsj.getStartMonth().toString().substring(0, 4));
		return setYxsjTj(lists, year);
	}

	/**
	 * 统计部门营销任务完成情况
	 */
	@Override
	public List<YxsjTj> excelData_tjbm(Yxsj yxsj) {
		String hql = "select new com.chinapost.vo.YxsjTj(t.tdept.cid,sum(t.cjf)) from Tyxsj t where 1=1 ";
		Map<String, Object> params = new HashMap<String, Object>();
		hql = addWhere(yxsj, hql, params);
		hql += " group by t.tdept.cid ";// 根据部门统计总积分
		List<YxsjTj> tjs = tjDao.find(hql, params);
		// 将得到的list转为Map
		Map<String, Double> tjmap = new HashMap<String, Double>();
		for (YxsjTj tj : tjs) {
			tjmap.put(tj.getCbh(), tj.getCzjf());
		}
		String dept_hql = "select new com.chinapost.vo.YxsjTj(t.cid,t.cname) from Tdept t where  t.cid in (:deptids) order by t.cseq";
		Map<String, Object> dept_params = new HashMap<String, Object>();
		dept_params.put("deptids", yxsj.getDeptIds());
		List<YxsjTj> lists = tjDao.find(dept_hql, dept_params);
		Integer year = Integer.parseInt(yxsj.getStartMonth().toString().substring(0, 4));// 年份，根据年份获取计划任务
		if (lists != null && lists.size() > 0) {
			for (YxsjTj tj : lists) {
				if (tjmap.get(tj.getDeptid()) != null) {// 如果员工有积分，则设置积分，没有则设为0
					tj.setCzjf(tjmap.get(tj.getDeptid()));
				} else {
					tj.setCzjf(0.0);
				}
				tj.setCjh(getDeptJhrw(tj.getDeptid(), year));
				if (tj.getCjh() > 0) {
					tj.setCbl(DoubleUtil.Keep2(tj.getCzjf() * 100 / tj.getCjh()));
				}
			}
		}
		Collections.sort(lists, new Comparator<YxsjTj>() {// 根据完成计划比例排序
					@Override
					public int compare(YxsjTj o1, YxsjTj o2) {
						if (o1.getCbl() == null) {
							o1.setCbl(0.0);
						}
						if (o2.getCbl() == null) {
							o2.setCbl(0.0);
						}
						return o2.getCbl().compareTo(o1.getCbl());// 降序，完成比例大的在前，如果是升序，则o1.compareto(o2)
					}
				});
		return lists;
	}

	private Double getDeptJhrw(String deptid, Integer year) {
		Double zjh = 0.0;
		String user_hql = " from Tuser t where t.tdept.cid =:deptid and t.ckind=2";
		Map<String, Object> user_params = new HashMap<String, Object>();
		user_params.put("deptid", deptid);
		List<Tuser> tusers = userDao.find(user_hql, user_params);
		if (tusers != null && tusers.size() > 0) {
			for (Tuser tuser : tusers) {
				String hql = "from Tjhrw t where t.tdept.cid =:deptid and t.cyear=:year and t.tyglb.cid =:yglbid";
				Map<String, Object> params = new HashMap<String, Object>();
				params.put("deptid", tuser.getTdept().getTdept().getCid());// 积分由所在部门的上级单位设置
				params.put("year", year);
				params.put("yglbid", tuser.getTyglb().getCid());
				Tjhrw tjhrw = jhrwDao.get(hql, params);
				if (tjhrw != null) {
					zjh += tjhrw.getCzjf();
				}
			}
		}
		return zjh;
	}

	/**
	 * 营销费用数据
	 */
	@Override
	public DataGrid datagrid_tjfy(Yxsj yxsj) {
		DataGrid dg = new DataGrid();
		String hql = "select new com.chinapost.vo.YxsjTj(t.cbh,sum(t.cjf),sum(t.cyxfy)) from Tyxsj t where 1=1 ";
		Map<String, Object> params = new HashMap<String, Object>();
		hql = addWhere(yxsj, hql, params);
		hql += " group by t.cbh order by  t.cbh";
		List<YxsjTj> list = tjDao.find(hql, params, yxsj.getPage(), yxsj.getRows());
		if (list != null && list.size() > 0) {
			for (YxsjTj yxsjtj : list) {
				String bh = yxsjtj.getCbh();
				Tuser tuser = getUserByBh(bh);
				yxsjtj.setDeptname(tuser.getTdept().getCname());
				yxsjtj.setUsername(tuser.getCname());
			}
		}
		dg.setRows(list);
		dg.setTotal(total_tjfy(yxsj));
		return dg;
	}

	private Long total_tjfy(Yxsj yxsj){
		String hql = "select new com.chinapost.vo.YxsjTj(t.cbh,sum(t.cjf),sum(t.cyxfy)) from Tyxsj t where 1=1 ";
		Map<String, Object> params = new HashMap<String, Object>();
		hql = addWhere(yxsj, hql, params);
		hql += " group by t.cbh ";
		List<YxsjTj> list = tjDao.find(hql, params);
		if (null != list && list.size() > 0){
			return (long)list.size();
		}
		return 0L;
	}
	/**
	 * 导出营销费用数据
	 */
	@Override
	public List<YxsjTj> excelData_tjfy(Yxsj yxsj) {
		String hql = "select new com.chinapost.vo.YxsjTj(t.cbh,sum(t.cjf),sum(t.cyxfy)) from Tyxsj t where 1=1 ";
		Map<String, Object> params = new HashMap<String, Object>();
		hql = addWhere(yxsj, hql, params);
		hql += " group by t.cbh order by  t.cbh";
		List<YxsjTj> list = tjDao.find(hql, params);
		// 将得到的list转为Map，方便查找
		Map<String, YxsjTj> tjmap = new HashMap<String, YxsjTj>();
		for (YxsjTj tj : list) {
			tjmap.put(tj.getCbh(), tj);
		}
		String user_hql = "select new com.chinapost.vo.YxsjTj(t.tdept.cname,t.cbh,t.cname) from Tuser t where t.ckind=2 and  t.tdept.cid in (:deptids) ";
		Map<String, Object> user_params = new HashMap<String, Object>();
		if (!StringUtils.isEmpty(yxsj.getCbh())){
			user_hql += " and t.cbh =:bh ";
			user_params.put("bh", yxsj.getCbh());
		}
		user_hql += " order by t.tdept.cseq,t.cbh";
		user_params.put("deptids", yxsj.getDeptIds());
		List<YxsjTj> lists = tjDao.find(user_hql, user_params);
		if (lists != null && lists.size() > 0) {
			for (YxsjTj tj : lists) {
				YxsjTj yxfytj = tjmap.get(tj.getCbh());
				if (yxfytj != null) {// 如果员工有营销积分和营销奖励，则添加，没有则设为0
					tj.setCzjf(yxfytj.getCzjf());
					tj.setCjh(yxfytj.getCjh());
				} else {
					tj.setCzjf(0.0);
					tj.setCjh(0.0);
				}
			}
		}
	   return lists;
	}
}
